Data_Class_Lab_3

library(RPostgreSQL)
library(ggplot2)
library(dplyr)
library(tidyr)
library(plotly)
library(cowplot)

dbListTables(con)

## [1] "cannibalism_assays" "field_surveys_copy" "field_surveys"     
## [4] "sweep_data"

myTable <- dbGetQuery(con, "SELECT field_surveys.sampling_date, field_surveys.field, field_surveys.crop, 
cannibalism_assays.bug_id, cannibalism_assays.healthy,cannibalism_assays.cannibalized,
cannibalism_assays.partial_,cannibalism_assays.crushed, cannibalism_assays.species
FROM field_surveys FULL OUTER JOIN cannibalism_assays ON (sampling_id = survey_id);")

myTable$cannibalism_rate <- myTable$cannibalized / (myTable$healthy + myTable$cannibalized)

plot1 <- myTable %>%
  filter(cannibalism_rate >= 0.1) %>%
  ggplot(aes(x=field, y=cannibalism_rate, fill = crop))+
  geom_violin()+
  theme(axis.text.x = element_text(angle = 90, hjust = 1))

plot2 <- myTable %>%
  ggplot(aes(x=field, y=cannibalism_rate, fill = crop))+
  geom_violin()+
  theme(axis.text.x = element_text(angle = 90, hjust = 1))

ggplotly(plot1)

## Warning: Removed 5 rows containing non-finite values (stat_ydensity).

ggplotly(plot2)

## Warning: Removed 628 rows containing non-finite values (stat_ydensity).